<?xml version="1.0"?>
<OnTheFlyInstruction xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <InstanceId>Shrink Logs</InstanceId>
  <Tags>
    <string>DB</string>
  </Tags>
  <AdditionalAssemblies />
  <Code><![CDATA[// v 1.0.0.1 - 2013-07-02 - Updated script for latest IF version
using xSolon.Instructions;
using System;using System.Data;
using System.Linq;using System.IO;
using xSolon.Instructions.SQL;
using System.Collections.Generic;

public class Sample1 : AbstractInstruction
{
	
	public Sample1(){ Tags = new string[] {"Generated"};}

    private DataTable DBCall(string sql)
    {
        var inst = new SqlServerInstruction();

		inst.NotifyEvent += NotifyGeneric;
		inst.Command = sql;
        inst.ConnectionString = "Data Source=sql3;Initial Catalog=master;Integrated Security=True";
        
        inst.Run();

        return inst.ResultTable;
    }

	public string GetRecoveryModel(string dbName)
	{
		var sql = string.Format(@"USE {0};
		SELECT recovery_model_desc 
		FROM sys.databases WHERE name = '{0}';
		", dbName);
		
		var table = DBCall(sql);
		
		var model = table.Rows[0][0].ToString();
		
		NotifyInformation("{0} Recovery model: {1}", dbName, model);
		
		return model;
	}
	
public List<string> GetLogFiles(string dbName)
{
	var template = 
		@"USE {0};
		-- get filenames 
		exec sp_helpfile
		";

	var table = DBCall(string.Format(template, dbName));
	
	ResultTable = table;
	
	var logFiles = new List<string>();

	if (table != null)
	foreach(DataRow row in table.Rows)
	{
		if (row["usage"].ToString().Contains("log"))
		{
			logFiles.Add(row["name"].ToString()); // Log file
		}
	}

	return logFiles;
}
	
	public string GenerateClearLogsScript(string dbName, List<string> dbFiles)
	{
		var template = 
		@"USE {0}; -- set database
		
-- print current recovery model
SELECT name, recovery_model_desc FROM sys.databases WHERE name = '{0}';

{2}
GO ----------------------------------------------------------------------

{1}

{3}
-- print current recovery model
SELECT name, recovery_model_desc FROM sys.databases WHERE name = '{0}';
GO ----------------------------------------------------------------------

";
		
		var recoveryModel = GetRecoveryModel(dbName);
		
		var fileClearTemplate = 
@"-- Shrink the log file 
DBCC SHRINKFILE ({0}, 1);
GO";
		
		var clearFiles = string.Empty;
		
		foreach(string filename in dbFiles)
		{
			clearFiles += string.Format(fileClearTemplate, filename);
		}
		
		return string.Format(template, dbName, clearFiles,
		(recoveryModel != "SIMPLE") ? 
			string.Format("-- set recovery model to simple\nALTER DATABASE {0} SET RECOVERY SIMPLE;\n", dbName) : "" ,
		(recoveryModel != "SIMPLE") ? 
			string.Format("-- roll back recovery model\nALTER DATABASE {0} SET RECOVERY {1};\n", dbName, recoveryModel) : ""
		);
	}

	public override void Run()
	{
		var databasesSql = @"
SELECT name 
FROM sys.databases 
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');";
		
		var table = DBCall(databasesSql);
		
		var fullScript = string.Empty;
		
		foreach(DataRow row in table.Rows)
		{
			try
			{
			var dbName = row[0].ToString();
			
			var logFiles = GetLogFiles(dbName);
			
			fullScript += GenerateClearLogsScript(dbName, logFiles);
			}
			catch(Exception ex)
			{
				NotifyException(ex);
			}
		}

		var fileName = Path.ChangeExtension(Path.GetTempFileName(), ".sql");
		
		File.WriteAllText(fileName, fullScript);
		
		Response = string.Format("-- Script Generated {0}\n{1}",
			fileName, fullScript);
	}

}]]></Code>
</OnTheFlyInstruction>